package cn.pzhuweb.dao.imp;

import cn.pzhuweb.dao.UserDAO;
import cn.pzhuweb.pojo.User;
import cn.pzhuweb.util.C3P0Util;
import cn.pzhuweb.util.DBUtil;
import cn.pzhuweb.util.DruidUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 实现持久化层
 * 实现子接口，完成jdbc的增删改查功能
 */
public class UserDAOImp implements UserDAO {
    private JdbcTemplate template = new JdbcTemplate(DruidUtil.getDs());

    @Override
    public int insert(User t) {
        // TODO Auto-generated method stub
        Connection con = C3P0Util.getConnection();
        PreparedStatement psta = null;
        String sql = "insert into user values(?,?)";
        try {
            psta = con.prepareStatement(sql);
            psta.setString(1, t.getName());
            psta.setString(2, t.getPassword());
            return psta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return 0;
    }

    @Override
    public int delete(String k) {
        // TODO Auto-generated method stub
        Connection con = DBUtil.getConnection();
        String sql = "DELETE FROM user WHERE name = ?";
        PreparedStatement psta = null;
        try {
            psta = con.prepareStatement(sql);
            psta.setString(1, k);
            return psta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return 0;
    }

    @Override
    public int update(User t) {
        // TODO Auto-generated method stub
        Connection con = DBUtil.getConnection();
        String sql = "UPDATE user SET password = ? WHERE name = ?";
        PreparedStatement psta = null;
        try {
            psta = con.prepareStatement(sql);
            psta.setString(2, t.getName());
            psta.setString(1, t.getPassword());
            return psta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return 0;
    }

    @Override
    public User selectById(String k) {
        Connection con = null;
        PreparedStatement psta = null;
        ResultSet res = null;
        String sql = "select * from user where name=?";
        try {
            con = DBUtil.getConnection();
            psta = con.prepareStatement(sql);
            psta.setString(1, k);
            res = psta.executeQuery();
            if (res.next()) {
                String name = res.getString(1);
                String password = res.getString(2);
                return new User(name, password);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(res, psta, con);
        }
        return null;
    }

    @Override
    public List<User> selectAll() {
        // TODO Auto-generated method stub
//        Connection con = DBUtil.getConnection();
//        PreparedStatement psta = null;
//        ResultSet res = null;
//        String sql = "select * from user";
//        try {
//            psta = con.prepareStatement(sql);
//            res = psta.executeQuery();
//            List<User> list = new ArrayList<>();
//            while (res.next()) {
//                String name = res.getString(1);
//                String password = res.getString(2);
//                list.add(new User(name, password));
//            }
//            return list;
//        } catch (SQLException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        } finally {
//            DBUtil.close(res, psta, con);
//        }
//        return null;
        String sql = "select  * from user";
        return template.query(sql, new BeanPropertyRowMapper<User>(User.class));
    }

}
